﻿/*****************************************
 * 
 *  copyright (c) by ylshen
 * 
 *  author : ylshen (syl.lucky@gmail.com)
 * 
 **************************************** */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using JYStockAnalyser.Model;

namespace JYStockAnalyser.DA
{
    /// <summary>
    /// 其它一些比较不规则的数据访问
    /// </summary>
    /// <remarks>这里指的不规则访问是指不是一个mapper对应一个实体的数据该问</remarks>
    public class OtherDA : ORMapper
    {
        public List<SecurityOrderItem> GetAllSecurityItems(YearQuarter quarter)
        {
            List<SecurityOrderItem> items = new List<SecurityOrderItem>();
            string sqlSelectStocks = "SELECT [no], [name] FROM t_stocks  WHERE substring(no,1,1) <> '9'  ORDER BY [no]";
            using (IDataReader reader = Excute_Reader(sqlSelectStocks))
            {
                while (reader.Read())
                {
                    SecurityOrderItem item = new SecurityOrderItem();
                    item.StockNO = reader.GetString(0);
                    item.StockName = reader.GetString(1);
                    items.Add(item);
                }
            }

            string sqlSelect11001Values = "SELECT stock, [value] FROM t_guidelines_11001 WHERE [date] = (SELECT MAX([date]) FROM t_guidelines_11001)";
            Dictionary<string, decimal> values = new Dictionary<string, decimal>();
            using (IDataReader reader = Excute_Reader(sqlSelect11001Values))
            {
                while (reader.Read())
                {
                    values.Add(reader.GetString(0), reader.GetDecimal(1));
                }
            }

            string sqlSelect20001Values = String.Format("SELECT stock, v3 FROM t_Guidelines_20001 WHERE year = {0} AND quarter = {1}", quarter.Year, quarter.Quarter);
            Dictionary<string, decimal> estimateValues = new Dictionary<string, decimal>();
            using (IDataReader reader = Excute_Reader(sqlSelect20001Values))
            {
                while (reader.Read())
                {
                    estimateValues.Add(reader.GetString(0), reader.GetDecimal(1));
                }
            }


            foreach (SecurityOrderItem item in items)
            {
                decimal value = GuideLine.UNDEFINED_VALUE;
                decimal estimateValue = GuideLine.UNDEFINED_VALUE;

                if (values.ContainsKey(item.StockNO))
                {
                    value = values[item.StockNO];
                }

                if (estimateValues.ContainsKey(item.StockNO))
                {
                    estimateValue = estimateValues[item.StockNO];
                }
                item.CurrentPrice = value;
                item.InsidePrice = estimateValue;
                item.Growth = FinancialCalculator.CalculateGrowthRate(item.CurrentPrice, item.InsidePrice);
            }
            return items;
        }     

        /// <summary>
        /// 获取所有股票在指定两个时间的净利润
        /// </summary>
        /// <remarks>两个时间的净利润用于计算净利润复合增长率</remarks>
        /// <param name="periodType"></param>
        /// <param name="beginQuarter"></param>
        /// <param name="endQuarter"></param>
        /// <returns></returns>
        public List<GrowthSpeedOrderItem> GetAllOrderItems(string periodType, int period, YearQuarter beginQuarter, YearQuarter endQuarter)
        {               

            List<GrowthSpeedOrderItem> items = new List<GrowthSpeedOrderItem>();
            string sqlSelectStocks = "SELECT [no], [name] FROM t_stocks WHERE substring(no,1,1) <> '9' ORDER BY [no]";
            using (IDataReader reader = Excute_Reader(sqlSelectStocks))
            {
                while (reader.Read())
                {
                    GrowthSpeedOrderItem item = new GrowthSpeedOrderItem();
                    item.StockNO = reader.GetString(0);
                    item.StockName = reader.GetString(1);
                    items.Add(item);
                }
            }

            string sqlSelectBeginValues = String.Format("SELECT stock, [value] FROM t_guidelines_10018 WHERE [year] = {0} AND [quarter] = {1} ORDER BY stock", beginQuarter.Year, beginQuarter.Quarter);
            Dictionary<string, decimal> beginValues = new Dictionary<string,decimal>();
            using (IDataReader reader = Excute_Reader(sqlSelectBeginValues))
            {
                while (reader.Read())
                {
                    beginValues.Add(reader.GetString(0), decimal.Round(reader.GetDecimal(1) / 10000m, 5));
                }
            }

            string sqlSelectEndValues = String.Format("SELECT stock, [value] FROM t_guidelines_10018 WHERE [year] = {0} AND [quarter] = {1} ORDER BY stock", endQuarter.Year, endQuarter.Quarter);
            Dictionary<string, decimal> endValues = new Dictionary<string,decimal>();
            using (IDataReader reader = Excute_Reader(sqlSelectEndValues))
            {
                while (reader.Read())
                {
                    endValues.Add(reader.GetString(0), decimal.Round(reader.GetDecimal(1) / 10000m ,5));
                }
            }

            foreach (GrowthSpeedOrderItem item in items)
            {
                decimal beginValue = GuideLine.UNDEFINED_VALUE;
                decimal endValue = GuideLine.UNDEFINED_VALUE;

                if (beginValues.ContainsKey(item.StockNO))
                {
                    beginValue = beginValues[item.StockNO];
                }

                if (endValues.ContainsKey(item.StockNO))
                {
                    endValue = endValues[item.StockNO];
                }

                item.GrowhCompoundRate = FinancialCalculator.CalculateCompoundGrowthRate(beginValue, endValue, period);
            }

            return items;
        }
    }
}
